Washington Data Codebook

1 Executive Summary

This codebook demonstrates the data ingestion and processing from Washington Department of Health’s Summary Financial Data. This includes:

• Balance Sheet
• Income Statement
• Financial Ratios
• FTE
• Rate Analysis
• Volume Trends

2 Data Processing

2.1 Hospital Names

2.2 Balance Sheet

##  [1] "total_current_assets"           "total_board_designated_assets" 
##  [3] "total_property_plant_equipment" "less_accumulated_depreciation" 
##  [5] "net_property_plant_equipment"   "total_investments_other_assets"
##  [7] "total_intangible_assets"        "total_assets"                  
##  [9] "total_current_liabilities"      "total_deferred_credits"        
## [11] "total_long_term_debt"           "less_current_maturities_of_ltd"
## [13] "net_long_term_debt"             "unrestricted_fund_balance"     
## [15] "total_equity"                   "total_liab_fund_bal_or_equity"

2.3 Financial Ratios

##  [1] "deductible"                     "markup"                        
##  [3] "operating_margin"               "total_margin"                  
##  [5] "expense_to_revenue"             "nonoperating_revenue"          
##  [7] "reported_income_index"          "return_on_total_assets"        
##  [9] "return_on_equity"               "growth_rate_in_equity"         
## [11] "current"                        "liabilities_to_assets"         
## [13] "days_in_patient_ar"             "average_payment_period"        
## [15] "days_cash_on_hand"              "equity_financing"              
## [17] "long_term_debt_to_equity"       "fixed_asset_financing"         
## [19] "cash_flow_to_total_debt"        "capital_expense"               
## [21] "times_interest_earned"          "debt_service_coverage"         
## [23] "long_term_debt_to_depreciation" "total_asset_turnover"          
## [25] "fixed_asset_turnover"           "current_asset_turnover"        
## [27] "inventory"                      "average_age_of_plant"          
## [29] "financial_viability_index"

2.4 FTE

2.5 Income Statement

##  [1] "inpatient_revenue"              "outpatient_revenue"            
##  [3] "total_patient_revenue"          "bad_debts"                     
##  [5] "contractual_adjustments"        "charity_care"                  
##  [7] "other_adjustments"              "total_deductions_from_revenue" 
##  [9] "net_patient_service_revenue"    "other_operating_revenue"       
## [11] "tax_revenue"                    "total_operating_revenue"       
## [13] "salaries_and_benefits"          "employee_benefits"             
## [15] "professional_fees"              "supplies"                      
## [17] "purchased_services_utilities"   "purchased_services_other"      
## [19] "depreciation"                   "rentals_and_leases"            
## [21] "insurance"                      "license_and_taxes"             
## [23] "interest"                       "other_direct_expenses"         
## [25] "total_operating_expenses"       "net_operating_revenue"         
## [27] "non_operating_income"           "net_before_extraordinary_items"
## [29] "extraordinary_items"            "federal_income_tax"            
## [31] "net_revenue_or_expense"

2.6 Rate Analysis

2.7 Volume Trends

##  [1] "births"                      "admissions"                 
##  [3] "patient_days"                "acmvus"                     
##  [5] "adj_adm"                     "adj_pat_days"               
##  [7] "lic_beds"                    "avail_beds"                 
##  [9] "intensive_care_patient_days" "acute_care_patient_days"    
## [11] "surgical_services_op_min"    "laboratory_cap_units"       
## [13] "radiology_rvus"              "ct_scanning_hect_units"     
## [15] "emergency_room_visits"

3 Feature Engineering

Out of all datasets, we construct a consolidated dataset based on selected features. In order to normalize the data (except financial ratios), we use:

\[denominator = caseMixIndex * admissions\]

3.1 Scribble

4 Models

We use random forest in order to predict and pinpoint factors influencing the following variables:

• Net revenue
• Operating margin
• ROE

We chose median absolute percentage error (MdAPE) to compare the effect of outliers on our performance.